

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.w3c.dom.Document;
import org.w3c.dom.Element;

public class ExcelToXML {
	
	
   public void generateXML(File excelFile, String compname) {
      try { //Initializing the XML document
         DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
         DocumentBuilder builder = factory.newDocumentBuilder();
         Document document = builder.newDocument();
         Element rootElement = document.createElement(compname);
         document.appendChild(rootElement);
            //Creating top-level elements
         Element stmtElement1 = document.createElement("DATA");
         rootElement.appendChild(stmtElement1);
            //Creating an HSSFSpreadsheet object from an Excel file
         InputStream input = new FileInputStream(excelFile);
         HSSFWorkbook workbook = new HSSFWorkbook(input);
         HSSFSheet spreadsheet = workbook.getSheetAt(0);
         

         for (int i = 0; i <= spreadsheet.getLastRowNum(); i++) {

         //Iterate over spreadsheet rows to create stmt element
         //subelements.
        	 try {
               HSSFRow row1 = spreadsheet.getRow(i);

         Element revenueElement1 = document.createElement("Row");
         revenueElement1.setAttribute("y", ""+i);
            stmtElement1.appendChild(revenueElement1);
            for (int j=0;j<=row1.getLastCellNum();j++) {
            	try {
            	Element Ceel = document.createElement("Cell");
            	Ceel.setAttribute("x", ""+j);
            	revenueElement1.appendChild(Ceel);
            		HSSFCell cell = row1.getCell((short) j);
            		Ceel.appendChild
                    (document.createTextNode(""+cell.getNumericCellValue()));
            	} catch (Exception e) {}
            	}
            

        	 } catch (Exception e) {}
            
         }

         TransformerFactory tFactory = TransformerFactory.newInstance();

         Transformer transformer = tFactory.newTransformer();
            //Add indentation to output
         transformer.setOutputProperty
         (OutputKeys.INDENT, "yes");
         transformer.setOutputProperty(
            "{http://xml.apache.org/xslt}indent-amount", "2");

         DOMSource source = new DOMSource(document);
         StreamResult result = new StreamResult(new File
        		 (excelFile.getAbsolutePath().split("data.xls")[0]+"\\data.xml"));
         transformer.transform(source, result);
      } catch (IOException e) {
         System.out.println("IOException " + e.getMessage());
      } catch (ParserConfigurationException e) {
         System.out
            .println("ParserConfigurationException " + e.getMessage());
      } catch (TransformerConfigurationException e) {
         System.out.println("TransformerConfigurationException "+ e.getMessage());
      } catch (TransformerException e) {
         System.out.println("TransformerException " + e.getMessage());
      }
   }

   public static void main(String[] argv) {
      ExcelToXML excel = new ExcelToXML();
      File input = new File("D:\\prace\\workspace\\CII_1edit\\CII_PART_DATA_TRANSCEIVER\\COMP_MEM\\AREA_MEMORY\\data.xls");
      excel.generateXML(input,"cos");
   }
   
}
